package work.linruchang.util;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.exceptions.ExceptionUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.util.ObjUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Page;
import cn.hutool.db.dialect.Dialect;
import cn.hutool.db.dialect.DialectFactory;
import cn.hutool.db.sql.SqlBuilder;
import cn.hutool.db.sql.SqlUtil;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.statement.select.SelectItem;
import work.linruchang.util.bean.Column;

import javax.sql.DataSource;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 增强的SQL工具
 *
 * @author LinRuChang
 * @version 1.0
 * @date 2022/11/21
 * @since 1.8
 **/
@Slf4j
public class EnhanceSqlUtil extends SqlUtil {

    /**
     * key数据库字段类型 value对应的
     */
    public static Map<String, Class> dbType2ClassMap = new HashMap<>();

    static {

    }

    @SneakyThrows
    private static void init() {
        InputStream resourceAsStream = EnhanceSqlUtil.class.getClassLoader().getResourceAsStream("/system/dict/dbTypeMappingDict.txt");
        ArrayList<String> dbTypeMappingDictInfos = IoUtil.readLines(resourceAsStream, StandardCharsets.UTF_8, new ArrayList<String>());
        dbTypeMappingDictInfos.forEach(dbTypeMappingDictInfo -> {
            List<String> currentInfos = StrUtil.split(dbTypeMappingDictInfo, StrUtil.COLON);
            String dbType = CollUtil.get(currentInfos, 0);
            String javaType = CollUtil.get(currentInfos, 1);
            dbType2ClassMap.put(dbType.toLowerCase(), ObjectUtil.defaultIfNull(EnhanceClassUtil.forName(javaType), String.class));
        });
    }


    /**
     * 去除多余SQL片段信息
     *
     * @param SQLFragment SQL片段
     * @return
     */
    public static String removeExcessSQLFragmentInfo(String SQLFragment) {
        if (StrUtil.isNotBlank(SQLFragment)) {
            SQLFragment = StrUtil.trim(SQLFragment);
            SQLFragment = StrUtil.trim(SQLFragment, 0, (currentChar) -> {
                return currentChar == '`' || currentChar == '\'';
            });
            return SQLFragment;
        }
        return SQLFragment;
    }

    /**
     * 表注释获取
     *
     * @param createTable
     * @return
     */
    public static String tableNameComment(CreateTable createTable) {
        if (createTable == null) {
            return null;
        }
        String result = null;
        List<String> tableOptionsStrings = createTable.getTableOptionsStrings();
        int tableNameCommentIndex = CollUtil.indexOf(tableOptionsStrings, elem -> {
            return StrUtil.equalsAnyIgnoreCase(elem, "COMMENT");
        });
        if (tableNameCommentIndex > -1) {
            result = removeExcessSQLFragmentInfo(CollUtil.get(tableOptionsStrings, tableNameCommentIndex + 2));
        }
        return removeExcessSQLFragmentInfo(result);
    }

    /**
     * 列注释
     *
     * @param columnDefinition
     * @return
     */
    public static String tableColumnComment(ColumnDefinition columnDefinition) {
        if (columnDefinition == null) {
            return null;
        }
        String result = null;
        List<String> columnSpecs = columnDefinition.getColumnSpecs();
        int columnCommentIndex = CollUtil.indexOf(columnSpecs, elem -> {
            return StrUtil.equalsAnyIgnoreCase(elem, "COMMENT");
        });
        if (columnCommentIndex > -1) {
            result = removeExcessSQLFragmentInfo(columnSpecs.get(columnCommentIndex + 1));
        }
        return removeExcessSQLFragmentInfo(result);
    }


    /**
     * SQL解析
     *
     * @param sql select以及create的SQL语句
     * @return
     */
    @SneakyThrows
    public static List<Column> parseSql(String sql) {
        List<Column> parseResult = null;
        if (StrUtil.isNotBlank(sql)) {
            Statement sqlStatement = CCJSqlParserUtil.parse(sql);
            if (sqlStatement instanceof CreateTable) {
                parseResult = parseSql((CreateTable) sqlStatement);
            } else if (sqlStatement instanceof Select) {
                parseResult = parseSql((Select) sqlStatement);
            } else if (sqlStatement instanceof Insert) {
                parseResult = parseSql((Insert) sqlStatement);
            } else {
                throw new RuntimeException("不支持解析的SQL语句");
            }
        }
        return CollUtil.emptyIfNull(parseResult);
    }

    public static List<Column> parseSql(CreateTable createTable) {
        List<Column> parseResult = new ArrayList<>();
        if (createTable != null) {
            String tableName = removeExcessSQLFragmentInfo(createTable.getTable().getName());
            String tableNameComment = tableNameComment(createTable);


            List<ColumnDefinition> columnDefinitions = CollUtil.emptyIfNull(createTable.getColumnDefinitions());
            columnDefinitions.forEach(columnDefinition -> {


                String columnName = removeExcessSQLFragmentInfo(columnDefinition.getColumnName());
                String columnDbFieldType = removeExcessSQLFragmentInfo(columnDefinition.getColDataType().getDataType()).toLowerCase();
                Class columnDbFieldJavaType = ObjectUtil.defaultIfNull(dbType2ClassMap.get(columnDbFieldType), String.class);

                String comment = tableColumnComment(columnDefinition);

                Column column = Column.builder()
                        .tableName(tableName)
                        .tableNameComment(tableNameComment)
                        .columnName(columnName)
                        .javaType(columnDbFieldJavaType)
                        .comment(comment)
                        .build();

                parseResult.add(column);

            });
        }
        return parseResult;
    }

    public static List<Column> parseSql(Select select) {
        List<Column> parseResult = new ArrayList<>();
        if (select != null) {
            PlainSelect selectBody = (PlainSelect) select.getSelectBody();

            List<SelectItem> selectItems = CollUtil.emptyIfNull(selectBody.getSelectItems());

            List<SelectExpressionItem> selectExpressionItems = Convert.toList(SelectExpressionItem.class, selectItems);

            String tableName = removeExcessSQLFragmentInfo(selectBody.getFromItem().toString());
            selectExpressionItems.forEach(selectExpressionItem -> {

                net.sf.jsqlparser.schema.Column jsqlparserColumn = (net.sf.jsqlparser.schema.Column) selectExpressionItem.getExpression();

                String columnName = removeExcessSQLFragmentInfo(jsqlparserColumn.getColumnName());


                parseResult.add(Column.builder()
                        .tableName(tableName)
                        .columnName(columnName)
                        .javaType(String.class)
                        .build());
            });
        }
        return parseResult;
    }

    public static List<Column> parseSql(Insert insert) {
        List<Column> parseResult = new ArrayList<>();
        if (insert != null) {
            Table table = insert.getTable();
            String tableName = removeExcessSQLFragmentInfo(table.getName());

            List<net.sf.jsqlparser.schema.Column> columns = insert.getColumns();
            columns.forEach(column -> {
                String columnName = removeExcessSQLFragmentInfo(column.getColumnName());
                parseResult.add(Column.builder()
                        .tableName(tableName)
                        .columnName(columnName)
                        .javaType(String.class)
                        .build());
            });
        }
        return parseResult;
    }

    public static String sqlCompressor(String sqlContent) {
        if (StrUtil.isNotBlank(sqlContent)) {
            sqlContent = StrUtil.removeAllLineBreaks(sqlContent);
            return StrUtil.trim(sqlContent);
        }
        return sqlContent;
    }

    /**
     * 是否是数据查询语句
     *
     * @param sql
     * @return
     */
    public static boolean isSelectSQL(String sql) {
        boolean selectSqlFlag = false;
        try {
            if(StrUtil.isNotBlank(sql)) {
                Statement sqlStatement = CCJSqlParserUtil.parse(sql);
                selectSqlFlag = sqlStatement instanceof Select;
            }
        } catch (Exception e) {
            log.warn("SQL解析异常【{}】：{}", sql, ExceptionUtil.stacktraceToOneLineString(e));
        }
        return selectSqlFlag;
    }

    /**
     * 生成分页的查询SQL语句
     * @param dataSource 数据源
     * @param selectSql 查询的SQL语句
     * @param pageNo 页数 空则为1
     * @param pageSize 页码 空则为10
     * @return
     */
    public static String createPageSql(DataSource dataSource, String selectSql, Integer pageNo, Integer pageSize) {
        Assert.isTrue(isSelectSQL(selectSql), "非查询语句【{}】,请检查", selectSql);

        if(StrUtil.containsAnyIgnoreCase(selectSql,"rownum", "limit", "offset")) {
            log.warn("SQL【{}】：已经含有分页参数，无须在添加", selectSql);
            return selectSql;
        }

        pageNo = ObjUtil.defaultIfNull(pageNo,1);
        pageSize = ObjUtil.defaultIfNull(pageSize,10);
        Page page = Page.of(pageNo-1, pageSize);
        SqlBuilder sqlBuilder = SqlBuilder.of(selectSql);

        Dialect dialect = DialectFactory.getDialect(dataSource);
        Assert.notNull(dialect, "未知数据库方言，请检查");

        sqlBuilder = ReflectUtil.invoke(dialect, "wrapPageSql", new Object[]{sqlBuilder, page});
        return sqlBuilder.build();
    }



}
